﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using TB.DataModel.DTO;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using TB.DataModel.DTO;

namespace TB.DAL
{
    public class DbHandler
    {
        public static SqlConnection Connection { get; set; }
        public static Database objDB { get; set; }

        //public static DbHandler Instance = new DbHandler();  

        public DbHandler()
        {
            Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["appsHarbor"].ConnectionString);
            objDB = new SqlDatabase(Connection.ConnectionString);
        }
        public void SaveMessageToDb(Message msg)
        {
            using (DbCommand objCMD = objDB.GetStoredProcCommand("usp_InsertMessage"))
            {
                objDB.AddInParameter(objCMD, "@Author", DbType.String, msg.Author);
                objDB.AddInParameter(objCMD, "@Email", DbType.String, msg.Email);
                objDB.AddInParameter(objCMD, "@Content", DbType.String, msg.Content);
                objDB.AddInParameter(objCMD, "@Guid", DbType.Guid, msg.Guid);

                try
                {
                    objDB.ExecuteNonQuery(objCMD);
                }

                catch (Exception ex)
                {
                    EventLog objLog = new EventLog();
                    objLog.WriteEntry(ex.Message);
                    throw ex;
                }
            }
        }

        public IList<Message> GetMessageBetweenDatesFromDb(DateTime fromDate, DateTime toDate)
        {

            IList<Message> msgs = new List<Message>();
            Message msg;
            using (DbCommand objCMD = objDB.GetStoredProcCommand("usp_GetMessagesBetweenDates"))
            {
                objDB.AddInParameter(objCMD, "@FromDate", DbType.DateTime, fromDate);
                objDB.AddInParameter(objCMD, "@ToDate", DbType.DateTime, toDate);

                try
                {
                    objDB.ExecuteNonQuery(objCMD);
                    using (IDataReader objDataReader = objDB.ExecuteReader(objCMD))
                    {
                        while (objDataReader.Read())
                        {
                            msg = new Message((string)objDataReader["Author"], (string)objDataReader["Email"], (string)objDataReader["Body"]);
                            msg.Rating = (int)objDataReader["Rating"];
                            msg.CreationDate = (DateTime)objDataReader["CreationDate"];
                            msgs.Add(msg);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventLog objLog = new EventLog();
                    objLog.WriteEntry(ex.Message);
                    throw ex;
                }
            }
            return msgs;
        }

        public Message GetMessageFromDb(Guid guid)
        {

            Message msg = null;
            using (DbCommand objCMD = objDB.GetStoredProcCommand("usp_GetMessage"))
            {
                objDB.AddInParameter(objCMD, "@Guid", DbType.Guid, guid);

                try
                {
                    objDB.ExecuteNonQuery(objCMD);
                    using (IDataReader objDataReader = objDB.ExecuteReader(objCMD))
                    {
                        while (objDataReader.Read())
                        {
                            msg = new Message((string)objDataReader["Author"], (string)objDataReader["Email"], (string)objDataReader["Content"]);                            
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventLog objLog = new EventLog();
                    objLog.WriteEntry(ex.Message);
                    throw ex;
                }               
            }
            return msg;
        }
    }
}
